package com.techventus.locations;

import java.util.List;

import com.techventus.server.voice.Voice;
import com.techventus.server.voice.datatypes.AllSettings;
import com.techventus.server.voice.datatypes.Phone;

//import com.google.android.maps.GeoPoint;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class SQLHelper {

	//static String dropLocation = "DROP TABLE LOCATION;";
	static String dropLocations = "DROP TABLE LOCATIONS;";
	static String dropGoogle = "DROP TABLE GOOGLE;";
	static String dropPhone = "DROP TABLE PHONE;";
	static String dropLocationPhoneEnable = "DROP TABLE LOCATIONPHONEENABLE;";
	static String dropCommand = "DROP TABLE COMMAND;";
	static String dropStatus = "DROP TABLE STATUS;";
	//static String dropServiceStatus = "DROP TABLE SERVICESTATUS;";
	//static String dropSettings = "DROP TABLE SETTINGS;";
	
	
	static String createLocations = "CREATE TABLE IF NOT EXISTS LOCATIONS (locationName VARCHAR PRIMARY KEY, locationLatitude FLOAT, locationLongitude FLOAT, locationRadius INTEGER, enabled BOOLEAN);";
	static String createLocationPhoneEnable =  "CREATE TABLE IF NOT EXISTS LOCATIONPHONEENABLE  (locationName VARCHAR, phoneName VARCHAR, phoneEnable INTEGER);";
	static String createPhone = "CREATE TABLE IF NOT EXISTS PHONE (phoneName VARCHAR PRIMARY KEY);";
	static String createGoogle = "CREATE TABLE IF NOT EXISTS GOOGLE (loginName VARCHAR PRIMARY KEY, password VARCHAR);";
    static String createCommand = "CREATE TABLE IF NOT EXISTS COMMAND (command VARCHAR, id INTEGER PRIMARY KEY AUTOINCREMENT);";
    static String createStatus = "CREATE TABLE IF NOT EXISTS STATUS ( time DATE,key VARCHAR PRIMARY KEY, status VARCHAR);";
  //  static String createSettings = "CREATE TABLE IF NOT EXISTS SETTINGS (key VARCHAR, value VARCHAR);";
    
    static String insertElsewhereLocation = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES ('Elsewhere',0,0, 1000,'true');";
   // static String insertTestLocations2 = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES ('testlocation2',20000000,20000000, 200,'true');";
  // static String insertTestLocations3 = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES  ('testlocation3',10000000,10000000, 100,'false');";
   // static String insertTestLocations4 = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES  ('testlocation4',30000000,10000000, 400,'true');";
    
  //  static String insertGooleCredentialsTest = "INSERT INTO GOOGLE (loginName,password) VALUES ('email','pass');";
    
    
    static String phoneInsertTest1 = "INSERT INTO PHONE (phoneName) VALUES ('android');";
    static String phoneInsertTest2 = "INSERT INTO PHONE (phoneName) VALUES ('gizmo');";
    static String phoneInsertTest3 = "INSERT INTO PHONE (phoneName) VALUES ('techventus');";
    static String phoneInsertTest4 = "INSERT INTO PHONE (phoneName) VALUES ('home');";
    static String phoneInsertTest5 = "INSERT INTO PHONE (phoneName) VALUES ('work');";
    
    static String locationPhoneEnableInsertTest1 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation1', 'android', 1);";
    static String locationPhoneEnableInsertTest2 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation1', 'gizmo', 1);";
    static String locationPhoneEnableInsertTest3 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation1', 'techventus', 0);";
    static String locationPhoneEnableInsertTest4 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation1', 'home', -1);";
    static String locationPhoneEnableInsertTest5 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation1', 'work', 2);";
    
    static String locationPhoneEnableInsertTest6 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation2', 'android', 2);";
    static String locationPhoneEnableInsertTest7 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation2', 'gizmo', 2);";
    static String locationPhoneEnableInsertTest8 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation2', 'techventus', 1);";
    static String locationPhoneEnableInsertTest9 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation2', 'home', 0);";
    static String locationPhoneEnableInsertTest10 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation2', 'work', 2);";
    
    static String locationPhoneEnableInsertTest11 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation3', 'android', 2);";
    static String locationPhoneEnableInsertTest12 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation3', 'gizmo', 2);";
    static String locationPhoneEnableInsertTest13 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation3', 'techventus', 1);";
    static String locationPhoneEnableInsertTest14 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation3', 'home', 1);";
    static String locationPhoneEnableInsertTest15 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation3', 'work', -1);";
    
    static String locationPhoneEnableInsertTest16 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation4', 'android', -1);";
    static String locationPhoneEnableInsertTest17 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation4', 'gizmo', -1);";
    static String locationPhoneEnableInsertTest18 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation4', 'techventus', -1);";
    static String locationPhoneEnableInsertTest19 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation4', 'home', -1);";
    static String locationPhoneEnableInsertTest20 = "INSERT INTO LOCATIONPHONEENABLE (locationName,phoneName,phoneEnable) VALUES ('testlocation4', 'work', -1);";
    
    static String updateGoogleConnectedStatement = "UPDATE STATUS SET status = 'Connected' WHERE key = 'google';";
    static String insertNoGoogleConnectionStatement = "INSERT INTO STATUS (key,status) VALUES ('google','No Connection');";
    static String insertNoGoogleCredentialsStatement = "INSERT INTO STATUS (key,status) VALUES ('google','No Login Credentials');";
    static String insertGoogleConnectedStatement = "INSERT INTO STATUS (key,status) VALUES ('google','Connected');";
    static String insertPowerLowStatement = "INSERT INTO STATUS (key,status) VALUES ('power','POWER_LOW');";
    static String insertAccuracyFineStatement = "INSERT INTO STATUS (key,status) VALUES ('accuracy','ACCURACY_COARSE');";
    
    static String updateNoGoogleConnectionStatement = "UPDATE STATUS SET status = 'No Connection' WHERE key = 'google';";
	static String selectStoredGoogleCredentialsStatement = "SELECT loginName,password FROM GOOGLE;";
	static String deleteGoogleConnectivityStatement = "DELETE FROM STATUS WHERE key = 'google';";
	
	
	static String selectLocationsStatement = "SELECT locationName,locationLatitude,locationLongitude,locationRadius, enabled FROM LOCATIONS;";
	
	static String selectElsewhereLocation = "SELECT * from LOCATIONS WHERE locationName = 'Elsewhere';";
	static String selectElsewhereLocationPhoneEnable = "SELECT * FROM LOCATIONPHONEENABLE WHERE locationName ='Elsewhere';";
	//static String insertElsewhereLocationPhoneEnableBase = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES ;";
	//static String insertElsewhereLocation = "INSERT INTO LOCATIONS (locationName,locationLatitude,locationLongitude,locationRadius, enabled) VALUES ('Elsewhere',0,0, 1000,'true');";
	
	
	
	
	static String initStatus = "INSERT INTO STATUS (key,status) VALUES ('google','No Credentials'), ('location','Unknown') ;" ;
	
	
	
	
	
	
	
	
    public static void exec(SQLiteDatabase db, String statement){
    	try{
    		Log.e("TECHVENTUS","EXECUTION: "+statement);
    		db.execSQL(statement);
    	}catch(Exception e){
    		Log.e("TECHVENTUS","EXECUTION: FAIL");
    		e.printStackTrace();
    	}
    }
    
    
    public static void establishTestDBSettings(SQLiteDatabase db){
    	/**/
    	//exec(db,dropLocation );
    	Log.e("TECHVENTUS","DROPPING TABLES");
    	//exec(db,dropLocations );
    	//exec(db,dropGoogle );
    	//exec(db,dropPhone );
    	//exec(db,dropLocationPhoneEnable );
    	//exec(db,dropCommand );
    	//exec(db,dropStatus );
    	//exec(db,dropServiceStatus );
    	//exec(db,dropSettings );
    	Log.e("TECHVENTUS","CREATING TABLES");
    	
    	exec(db,createLocations );
    	exec(db,createLocationPhoneEnable );
    	exec(db,createPhone );
    	exec(db,createGoogle );
    	exec(db,createCommand );
    	exec(db,createStatus );
    	
    //	exec(db,createSettings );

    	//exec(db,  insertTestLocations1 );
    	//exec(db,  insertTestLocations2 );
    	//exec(db,  insertTestLocations3 );
    	//exec(db,  insertTestLocations4 );
    	
    	//exec(db,  insertGooleCredentialsTest );
    
    	//exec(db,  phoneInsertTest1 );
    	//exec(db,  phoneInsertTest2 );
    	//exec(db,  phoneInsertTest3 );
    	//exec(db,  phoneInsertTest4 );
    	//exec(db,  phoneInsertTest5 );
    
    	//exec(db,  locationPhoneEnableInsertTest1 );
    	//exec(db,  locationPhoneEnableInsertTest2 );
    	//exec(db,  locationPhoneEnableInsertTest3 );
    	//exec(db,  locationPhoneEnableInsertTest4 );
    	//exec(db,  locationPhoneEnableInsertTest5 );
/*
    	exec(db,  locationPhoneEnableInsertTest6 );
    	exec(db,  locationPhoneEnableInsertTest7 );
    	exec(db,  locationPhoneEnableInsertTest8 );
    	exec(db,  locationPhoneEnableInsertTest9 );
    	exec(db,  locationPhoneEnableInsertTest10 );
    
    	exec(db,  locationPhoneEnableInsertTest11 );
    	exec(db,  locationPhoneEnableInsertTest12 );
    	exec(db,  locationPhoneEnableInsertTest13 );
    	exec(db,  locationPhoneEnableInsertTest14 );
    	exec(db,  locationPhoneEnableInsertTest15 );
   
    	exec(db,  locationPhoneEnableInsertTest16 );
    	exec(db,  locationPhoneEnableInsertTest17 );
    	exec(db,  locationPhoneEnableInsertTest18 );
    	exec(db,  locationPhoneEnableInsertTest19 );
    	exec(db,  locationPhoneEnableInsertTest20 );
*/
    }
    
    
    public static void reportTable(SQLiteDatabase sql, String query, int dimension ){
    	System.out.println("Printing Table "+query+" dimension:"+dimension);
    	try{
    	Cursor c = sql.rawQuery(query, null);
    	if(c!=null){
    		while(c.moveToNext()){
    			for(int i=0;i<dimension;i++){
    				System.out.println(c.getString(i)+" , ");
    			}
    			System.out.println();
    		}
    		c.close();
    	}
    	
    	}catch(Exception y){
    		y.printStackTrace();
    	}
    }
    
    
    public static void listVoicePhoneList(Voice voice){
    	try{
    		Log.e("TECHVENTUSPHONE","Attempt to listVoicePhone");
    		if(voice!=null){
    			AllSettings voiceSettings = voice.getSettings(false);
    			if(voiceSettings!=null){
        			if(voiceSettings.getPhones()!=null){
        				if(voiceSettings.getPhones().length>0){
        		    		Log.e("TECHVENTUSPHONE","LISTING Voice.phoneList");
        		    		for(Phone phone:voiceSettings.getPhones()){
        		    			Log.e("TECHVENTUSPHONE","voice.phone "+phone.getName());
        		    		}
        		    		Log.e("TECHVENTUSPHONE","Done LISTING Voice.phoneList");
        				}else{
        					Log.e("TECHVENTUSPHONE","Error.  phoneList Not NULL, but Empty");
        				}
        			}else{
        				Log.e("TECHVENTUSPHONE","Error.  phoneList is Null");
        			}
    			}else{
    				Log.e("TECHVENTUSPHONE","Error.  voice AllSettings is Null");
    			}

    		}else{
    			Log.e("TECHVENTUSPHONE","Error.  Voice si NULL");
    		}
    		

    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }
    
    public static void listPhoneAndLocationPhoneEnable(SQLiteDatabase sql){
    	Log.e("TECHVENTUSPHONE","BEGINGING LIST PHONE AND LOCATIONPHONEENABLE");
    	try{
    		try{
    			Cursor c = sql.rawQuery("SELECT * FROM PHONE;", null);
    			if(c!=null){
    				Log.e("TECHVENTUSPHONE","Cursor NOT NULL SELECT * FROM PHONE block...Printing output");
    				while (c.moveToNext()){
    					Log.e("TECHVENTUSPHONE","Phone : "+c.getString(0));
    				}
    				Log.e("TECHVENTUSPHONE","DONE PRINTING PHONE block");
    				c.close();
    			}else{
    				Log.e("TECHVENTUSPHONE","Null cursor in SELECT * FROM PHONE block");
    			}
        	}catch(Exception u){
        		Log.e("TECHVENTUSPHONE","Exception with SELECT * FROM PHONE block");
        		u.printStackTrace();
        	}
    		try{
    			Cursor c = sql.rawQuery("SELECT * FROM LOCATIONS;", null);
    			if(c!=null){
    				Log.e("TECHVENTUSPHONE","Cursor NOT NULL SELECT * FROM LOCATIONS block...Printing output");
    				while (c.moveToNext()){
    					Log.e("TECHVENTUSPHONE","locationName: "+c.getString(0));
    				}
    				Log.e("TECHVENTUSPHONE","DONE PRINTING LOCATIONS block");
    				c.close();
    			}else{
    				Log.e("TECHVENTUSPHONE","Null cursor in SELECT * FROM LOCATIONS block");
    			}
    			
        	}catch(Exception u){
        		Log.e("TECHVENTUSPHONE","Exception with SELECT * FROM LOCATIONS");
        		u.printStackTrace();
        	}
        	
        	
    		try{
    			Cursor c = sql.rawQuery("SELECT * FROM LOCATIONPHONEENABLE;", null);
    			if(c!=null){
    				Log.e("TECHVENTUSPHONE","Cursor NOT NULL SELECT * FROM LOCATIONPHONEENABLE block...Printing output");
    				while (c.moveToNext()){
    					Log.e("TECHVENTUSPHONE","locationName: "+c.getString(0)+" phoneName: "+c.getString(1)+" phoneEnabled: "+c.getInt(2));
    				}
    				Log.e("TECHVENTUSPHONE","DONE PRINTING LOCATIONPHONEENABLE block");
    				c.close();
    			}else{
    				Log.e("TECHVENTUSPHONE","Null cursor in SELECT * FROM LOCATIONPHONEENABLE block");
    			}
    			
        	}catch(Exception u){
        		Log.e("TECHVENTUSPHONE","Exception with SELECT * FROM LOCATIONPHONEENABLE");
        		u.printStackTrace();
        	}
        	
        	
        	
        	
        	Log.e("TECHVENTUSPHONE","FINISHED LIST PHONE AND LOCATIONPHONEENABLE");
        	
    	}catch(Exception u){
    		Log.e("TECHVENTUSPHONE","OUTER ERROR");
    		u.printStackTrace();
    	}
    }
    
    
    
    public static void createElsewhere(SQLiteDatabase sql, List<String> phoneList){
    	//boolean cont =false;
    	try{
    		
    		
    		
    		
    		/*
    		Cursor c =sql.rawQuery(selectElsewhereLocation, null);
    		 if(c!=null){
				 c.moveToNext();
				 if( !c.getString(0).equals("Elsewhere")){
					 Log.e("TECHVENTUS","Location Deleted");
					 sql.execSQL("DELETE FROM LOCATIONS ;");
					 sql.execSQL(insertElsewhereLocation);
					 cont = true;
				 }else{
					 cont =true;
				 }
				 String s = c.getString(0);
    		 }else{
    		 */
    			 Log.e("TECHVENTUS", "PROPER INSERT ELSEWHERE");
    			 sql.execSQL(insertElsewhereLocation); 
    			
    		//	 cont = true;
    		// }
    	}catch(Exception e){
    		//try{
    		e.printStackTrace();
    		// sql.execSQL(insertElsewhereLocation);
    		// cont = true;
    		//}catch(Exception o){
    		//	o.printStackTrace();
    			//sql.execSQL(dropLocations);
    			//sql.execSQL(createLocations);
    			//sql.execSQL(insertElsewhereLocation); 
    		}
    	}

    	
    
}
    
    
    
    
    
    
    
    
    
    
    

